﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
///SqlHelper 的摘要说明
/// </summary>
class SqlHelper
{
    //定义连接字符串
    private static string connStr = ConfigurationManager.AppSettings["TzysMySqlConnectionString"];
    private static string sqlConnStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();

    //做连接
    private static SqlConnection SqlConn;
    
    /// <summary>
    /// 打开连接
    /// </summary>
    /// <returns></returns>
    public static SqlConnection OpenSqlConn() {
        if (SqlConn == null)
        {
            SqlConn = new SqlConnection(sqlConnStr);
            SqlConn.Open();
        }
        if (SqlConn.State == System.Data.ConnectionState.Closed)
            SqlConn.Open();
        return SqlConn;
    }
    
    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public static void Close()
    {
        if (SqlConn != null)
        {
            SqlConn.Close();
            SqlConn = null;
        }
    }

    //定义标准增删改方法
    public static int ExecuteNonQuery(String sql, params MySqlParameter[] parameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connStr))
        {
            connection.Open();
            using (MySqlCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                return command.ExecuteNonQuery();
            }
        }
    }
    //聚合函数的查询
    public static Object ExecuteScalar(String sql, params MySqlParameter[] parameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connStr))
        {
            connection.Open();
            using (MySqlCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                return command.ExecuteScalar();
            }
        }
    }

    //进行DataTable的查询
    public static DataTable ExecuteDataTable(String sql, params MySqlParameter[] parameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connStr))
        {
            connection.Open();
            using (MySqlCommand command = connection.CreateCommand())
            {
                command.CommandText = sql;
                command.Parameters.AddRange(parameters);
                MySqlDataAdapter adaper = new MySqlDataAdapter(command);
                DataSet dataSet = new DataSet();
                adaper.Fill(dataSet);
                return dataSet.Tables[0];
            }
        }
    }
   

    #region
    //基本的封装一些常用方法：
    public static ArrayList DataTable2ArrayList(DataTable data)
    {
        //定义一个数组
        ArrayList array = new ArrayList();
        //开始遍历循环
        for (int i = 0; i < data.Rows.Count; i++)
        {
            //开始取出每行对象
            DataRow row = data.Rows[i];
            //定义一个map
            Hashtable record = new Hashtable();
            for (int j = 0; j < data.Columns.Count; j++)
            {
                //开始取第i行的每列值
                object cellValue = row[j];
                //判断是否为DBNull
                if (cellValue.GetType() == typeof(DBNull))
                {
                    cellValue = null;
                }
                //map的key为列名，值为当前值（如：name:张三,age:32.。。。）
                record[data.Columns[j].ColumnName] = cellValue;
            }
            //装入数组的每一格，每一格都是一个map对象
            array.Add(record);
        }
        return array;
    }

    //基本的封装一些常用方法HashTable的意思就是在以上数组上装一个data的key,数据还是arraylist：
    public static Hashtable DataTable2HashTable(DataTable data)
    {
        //定义一个hashtable供返回
        Hashtable hashTable = new Hashtable();
        ArrayList array = DataTable2ArrayList(data);
        hashTable["data"] = array;
        return hashTable;
    }
  
    #endregion

    #region
    //转换DBNULL方式
    public static Object toDBNull(Object obj)
    {
        if (obj == null)
        {
            return DBNull.Value;
        }
        else
        {
            return obj;
        }
    }
    public static Object fromDBNull(Object obj)
    {
        if (obj == DBNull.Value)
        {
            return null;
        }
        else
        {
            return obj;
        }
    }
    #endregion
}